package com.util;

import java.sql.*;
import java.util.*;

public class SQLHelp {
    static Connection conn = null;
    static long lastConnectSeconds;

//    // MySQL 8.0 以下版本 - JDBC 驱动名及数据库 URL
//    static final String JDBC_DRIVER = "com.mysql.jdbc.Driver";
//    static final String DB_URL = "jdbc:mysql://localhost:3306/newfolder";

    // MySQL 8.0 以上版本 - JDBC 驱动名及数据库 URL

    // 这部分放到了mysql.properties中
//    static final String JDBC_DRIVER = "com.mysql.cj.jdbc.Driver";
//    static final String DB_URL = "jdbc:mysql://43.139.139.78:3306/newfolder?useSSL=false&allowPublicKeyRetrieval=true&serverTimezone=UTC";
//
//    // 数据库的用户名与密码，需要根据自己的设置
//    static final String USER = "root";
//    static final String PASS = "clsjroot";

    public static Connection getConn(){
        if(conn == null){
            try {
                Class.forName(JdbcUtil.driver);
                System.out.println("连接数据库...");
                conn = DriverManager.getConnection(JdbcUtil.url,JdbcUtil.user,JdbcUtil.password);
                lastConnectSeconds = System.currentTimeMillis() / 1000;
            } catch (ClassNotFoundException | SQLException e) {
                e.printStackTrace();
            }
        }
        try {
            if(conn.isClosed()){
                conn = DriverManager.getConnection(JdbcUtil.url,JdbcUtil.user,JdbcUtil.password);
                lastConnectSeconds = System.currentTimeMillis() / 1000;
            }
            long now = System.currentTimeMillis() / 1000;
            if(now - lastConnectSeconds >= 3600 * 8){
                // 需要重连
                try{
                    conn.close();
                }catch (SQLException e){
                    // 不用管
                }finally {
                    conn = DriverManager.getConnection(JdbcUtil.url,JdbcUtil.user,JdbcUtil.password);
                    lastConnectSeconds = System.currentTimeMillis() / 1000;
                }
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        }
        return conn;
    }

    public static void close(){
        try {
            if(!conn.isClosed()){
                conn.close();
            }
        } catch (SQLException e) {
            e.printStackTrace();
        }finally {
            conn = null;
        }
    }


    //增
    //删
    //改
    public static int update(String sql) {
        // https://blog.csdn.net/cherishme1988/article/details/7399225
        // 涉及到update\insert的用executeUpdate
        try {
            Statement stmt = getConn().createStatement();
            int rs = stmt.executeUpdate(sql);
            stmt.close();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }return 0;
    }
    //增删改
    public static boolean execute(String sql) {
        // https://blog.csdn.net/cherishme1988/article/details/7399225
        // 涉及到update\insert的用execute
        try {
            Statement stmt = getConn().createStatement();
            // 【注】创建表的sql语句会返回false，因为创建表不会返回结果，返回了空，java中就转换成了false，但是表是正确创建的
            // https://blog.csdn.net/weixin_44889894/article/details/112321603
            boolean rs = stmt.execute(sql);
            stmt.close();
            return rs;
        } catch (SQLException e) {
            e.printStackTrace();
        }return false;
    }


    // 查
    public static void query(String sql) {
        try {
            Statement stmt = getConn().createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            stmt.close();
            // 完成后关闭
            rs.close();
        } catch (SQLException e) {
            e.printStackTrace();
        }
    }


    public static ArrayList<HashMap<String,String>> selectQuery(String sql){
        Statement stmt;
        try {
            stmt = getConn().createStatement();
            ResultSet rs = stmt.executeQuery(sql);
            ResultSetMetaData md = rs.getMetaData();//获取表结构
            int columnCount = md.getColumnCount();//获取列数
            ArrayList<HashMap<String,String>> list = new ArrayList<>();
            // 展开结果集数据库
            while (rs.next()) {
                HashMap<String, String> hashMap = new HashMap<>();
                for (int i = 1; i <= columnCount; ++i){
                    hashMap.put(md.getColumnName(i), rs.getString(i));
                }
                list.add(hashMap);
            }
            rs.close();
            stmt.close();
            return list;
        } catch (SQLException e){
            e.printStackTrace();
            return new ArrayList<>();
        }
    }

    public static void test(){
        // 测试创建表
        String sql = "create table if not exists test(" +
                "test_id int auto_increment," +
                "primary key(test_id)" +
                ")";
        execute(sql);
    }
}
